package dao;

import entity.User;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import utils.DataSourceUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

public class UserDao extends BaseDao {


    /**
     * 根据用户名查询用户
     * @param username
     * @return
     */
    public User fingByUsername(String username) {

        BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        try {
            return jdbcTemplate.queryForObject("select * from user where username=?",rowMapper,username);
        } catch (DataAccessException e) {

        }
        return null;
    }

    public User fingByEmail(String email) {
        BeanPropertyRowMapper<User>rowMapper =new BeanPropertyRowMapper<>(User.class);
        try {
            return jdbcTemplate.queryForObject("select * from user where email=?",rowMapper,email);
        }catch (DataAccessException e) {

        }
        return null;
    }


    /**
     * 用户注册
     * @param user
     */
    public void register(User user) {
        jdbcTemplate.update("insert into user values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                user.getUsername(),
                user.getPassword(),
                user.getEmail(),
                user.getQqOpenid(),
                user.getWxOpenid(),
                user.getRealName(),
                user.getAge(),
                user.getPhone(),
                user.getGender(),
                user.getInfo(),
                user.getRegisterTime(),
                user.getLoginTime(),
                user.getPic(),
                user.getLook(),
                user.getIsSecret(),
                user.getDeptName(),
                user.getDeptId()
        );

    }

    public List<User> findAll() {

        return jdbcTemplate.query("select * from user",new BeanPropertyRowMapper<>(User.class));
    }

    /**
     * 条件搜索
     * @param searchVal
     * @return
     */
    public List<User> search(String searchVal) {
        return jdbcTemplate.query("select * from user where username like ? or real_name like ?",
                new BeanPropertyRowMapper<>(User.class),"%"+searchVal+"%","%"+searchVal+"%" );
    }

    /**
     * 条件搜索+分页
     * @param username
     * @param startIndex
     * @param pageSize
     * @return
     */
    public List<User> findPage(String username, Integer startIndex, Integer pageSize) {
        return jdbcTemplate.query("select * from user where username like ? limit ?,?",
                new BeanPropertyRowMapper<>(User.class),"%"+username+"%",startIndex,pageSize);
    }

    /**
     * 根据用户名查询符合记录的条数
     * @param username
     * @return
     */
    public Integer countByUsername(String username) {
        return jdbcTemplate.queryForObject("select count(1) from user where username like ?",Integer.class,"%"+username+"%");
    }

    /**
     * 根据用户id查询员工
     * @param deptId
     * @return
     */
    public List<User> findByDeptId(Long deptId) {
         return jdbcTemplate.query("select * from user where dept_id =?",new BeanPropertyRowMapper<>(User.class),deptId);
    }

    /**
     * 查询用户详情
     * @param userId
     * @return
     */
    public User findById(long userId) {
        return jdbcTemplate.queryForObject("select * from user where id =?",new BeanPropertyRowMapper<>(User.class),userId);
    }

    /**
     * 查询用户粉丝数
     * @param userId
     * @return
     */
    public Integer findFansCount(long userId) {
        return jdbcTemplate.queryForObject("select count(1) from userfocus where user_focus_id =?",Integer.class,userId);

    }

    /**
     * 被看数+1
     * @param userId
     */
    public void updateLook(long userId) {
         jdbcTemplate.update("update user set look=look+1 where id=?",userId);

    }

    /**
     * 查询用户关注数
     * @param userId
     * @return
     */
    public Integer findFocusCount(Long userId) {
        return jdbcTemplate.queryForObject("select count(1) from userfocus where user_id =?",Integer.class,userId);

    }

    /**
     * 更新数据库
     * @param user
     */
    public void update(User user) {
         jdbcTemplate.update(
                 "update user set real_name=?,age=?,phone=?,gender=?,info=?,is_secret=?,dept_name=?,dept_id=? where id=?",
                 user.getRealName(),
                 user.getAge(),
                 user.getPhone(),
                 user.getGender(),
                 user.getInfo(),
                 user.getIsSecret(),
                 user.getDeptName(),
                 user.getDeptId(),
                 user.getId());
    }

    public void updatePassword(User user) {
        jdbcTemplate.update(
                "update user set password=? where id=?",
                user.getPassword(),
                user.getId());
    }

    /**
     * 更新图片地址
     * @param id
     * @param url
     */
    public void updatePic(Long id, String url) {
        jdbcTemplate.update("update user set pic=? where id=?",url,id);
    }

    /**
     * 根据id查询这个用户关注的用户列表
     * @param id
     * @return
     */
    public List<Long> findFocusIdList(Long id) {
        //List<T>
        ResultSetExtractor<List<Long>> setExtractor = new ResultSetExtractor<List<Long>>() {

            @Override
            public List<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {

                List<Long> userIdList = new ArrayList<>();

                while(rs.next()){
                    userIdList.add(rs.getLong("user_focus_id"));
                }
                return userIdList;
            }
        };



        return jdbcTemplate.query("select user_focus_id from userfocus where user_id=?",setExtractor,id);
    }

    /**
     * 查询是否关注过对方
     * @param userId
     * @param focusId
     * @return
     */
    public Boolean isFocus(Long userId, Long focusId) {

        Integer count = jdbcTemplate.queryForObject("select count(1) from userfocus where user_id=? and user_focus_id=?",Integer.class,userId,focusId);

        return count > 0 ;
    }

    public void delFocus(Long userId, Long focusId) {

        jdbcTemplate.update("delete from userfocus where user_id=? and user_focus_id=?",userId,focusId);
    }

    public void addFocus(Long userId, Long focusId) {

        jdbcTemplate.update("insert into userfocus values(?,?)",userId,focusId);
    }

    /**
     * 我的关注列表+分页
     * @param userId
     * @param startIndex
     * @param pageSize
     * @return
     */
    public List<User> findFocusPage(Long userId, Integer startIndex, Integer pageSize) {

        String sql = "select * from user where id in (select user_focus_id from userfocus where user_id=?) limit ?,?";


        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),userId,startIndex,pageSize);
    }

    /**
     * 根据用户id查询关注的人数
     * @param userId
     * @return
     */
    public Integer countFocusByUserId(Long userId) {

        return jdbcTemplate.queryForObject("select count(1) from userfocus where user_id=?",Integer.class,userId);
    }

    /**
     * 更改用户登录时间
     * @param userId
     */
    public void updateLoginTime(Long userId) {
        jdbcTemplate.update("update user set login_time=now() where id=?",userId);
    }

    /**
     * 根据微信的openId查询用户
     * @param openid
     * @return
     */
    public User findByWxOpenId(String openid) {
        try {
            return jdbcTemplate.queryForObject("select * from user where wx_openid=?",new BeanPropertyRowMapper<>(User.class),openid);
        } catch (DataAccessException e) {

        }
        return null;
    }
}
